package com.xqgl.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.xqgl.bean.Seat;
import com.xqgl.dao.SeatDao;
import com.xqgl.util.ConnertionUtil;

public class SeatDaoImp implements SeatDao{
	Connection conn = ConnertionUtil.getConnection();
	PreparedStatement ps;
	Statement statement;
	
	public ArrayList<Seat> getAllUserInfos() {
		ArrayList<Seat> seats = new ArrayList<>();
		String sql = "select * from seat ";
		try {
			ps = conn.prepareStatement(sql);
			ResultSet rst=ps.executeQuery(sql);
			rst.last();
			rst.beforeFirst();
			while(rst.next()) {
				Seat seat = new Seat();
				seat.setId(rst.getInt("id"));
				seat.setVillagename(rst.getString("villagename"));
				seat.setSeatnum(rst.getString("seatnum"));
				seat.setCarnum(rst.getString("carnum"));
				seat.setCarpicture(rst.getString("carpicture"));
				seat.setUsername(rst.getString("username"));
				seat.setUserphone(rst.getInt("userphone"));
				seat.setNature(rst.getString("nature"));
				seat.setTotalcost(rst.getString("totalcost"));
				seat.setStarttime(rst.getString("starttime"));
				seat.setDeadline(rst.getString("deadline"));
				seat.setSeatctime(rst.getString("seatctime"));
				seats.add(seat);
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return seats;
	}
	
	@Override
	public int add(Seat seat) throws SQLException {
		String sql = "insert into seat(villagename,seatnum,carnum,carpicture,username,userphone,nature,totalcost,starttime,deadline,seatctime)"
				+ "values(?,?,?,?,?,?,?,?,?,?,?)";
		ps = conn.prepareStatement(sql);
		ps.setString(1, seat.getVillagename());
		ps.setString(2, seat.getSeatnum());
		ps.setString(3, seat.getCarnum());
		ps.setString(4, seat.getCarpicture());
		ps.setString(5, seat.getUsername());
		ps.setInt(6, seat.getUserphone());
		ps.setString(7, seat.getNature());
		ps.setString(8, seat.getTotalcost());
		ps.setString(9, seat.getStarttime());
		ps.setString(10, seat.getDeadline());
		ps.setString(11, seat.getSeatctime());
		int a = ps.executeUpdate();
		ps.close();
		return a;
	}

	@Override
	public int update(Seat seat) throws SQLException {
		String sql = "update seat set villagename = '"+seat.getVillagename()+"', seatnum = '"+seat.getSeatnum()+"', carnum = '"+seat.getCarnum()+"',"
				+ "carpicture = '"+seat.getCarpicture()+"', username = '"+seat.getUsername()+"', userphone = "+seat.getUserphone()+", nature = '"+seat.getNature()+"',"
						+ "totalcost = '"+seat.getTotalcost()+"', starttime = '"+seat.getStarttime()+"', deadline = '"+seat.getDeadline()+"', seatctime = '"+seat.getSeatctime()+"' where id = "+seat.getId()+"";
		ps = conn.prepareStatement(sql);
		int b = ps.executeUpdate();
		ps.close();
		return b;
	}

	@Override
	public boolean delete(int id) throws SQLException {
		String sql = "delete from seat where id = "+id+"";
		statement = conn.createStatement();
		boolean c = statement.execute(sql);
		statement.close();
		return !c;
	}

	@Override
	public Seat findById(int id) throws SQLException {
		Seat seat = new Seat();
		String sql = "select * from seat where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet set = ps.executeQuery();
			while(set.next()){
				seat.setId(set.getInt("id"));
				seat.setVillagename(set.getString("villagename"));
				seat.setSeatnum(set.getString("seatnum"));
				seat.setCarnum(set.getString("carnum"));
				seat.setCarpicture(set.getString("carpicture"));
				seat.setUsername(set.getString("username"));
				seat.setUserphone(set.getInt("userphone"));
				seat.setNature(set.getString("nature"));
				seat.setTotalcost(set.getString("totalcost"));
				seat.setStarttime(set.getString("starttime"));
				seat.setDeadline(set.getString("deadline"));
				seat.setSeatctime(set.getString("seatctime"));
			}
			ps.close();
			return seat;
	}

}
